set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER PROCEDURE  [dbo].[prcCursorINVITEM]
@RID numeric(18,0)
AS
SET NOCOUNT ON
declare @QTYvarX as numeric(18,2)
declare @QTYvar as numeric(18,2)
declare @CostvarNew as numeric(18,2)
declare @UnitCount int
DECLARE @iRowIdX  int,@vchInv_noX  numeric(18,0),@vchbatchX  numeric(18,0),@vchInv_dateX datetime, @vchQty_inX numeric(18,0), @vchQty_OutX numeric(18,0),@vchCostX  numeric(18,2),@vchCost_AvgX numeric(18,2),@vchCodeX varchar(1000)
DECLARE @iRowId  int,@vchInv_no  numeric(18,0),@vchInv_date datetime, @vchQty_in numeric(18,0), @vchQty_Out numeric(18,0),@vchCost numeric(18,2)
DECLARE asSalesBatch CURSOR FOR                                                        

SELECT    iRowId,inv_no,batch,inv_date,qty_in, qty_out, cost,cost_avg,code FROM asSalesBatch  WHERE IROWID = @RID  order by code,inv_date,IROWID
update asbatch set qty_out=0
delete from assales
OPEN asSalesBatch 
FETCH asSalesBatch INTO @iRowIdX,@vchInv_noX,@vchbatchX,@vchInv_dateX,@vchqty_inX, @vchqty_outX, @vchcostX,@vchCost_AvgX,@vchCodeX
WHILE @@Fetch_Status = 0
BEGIN

		DECLARE asBatch CURSOR FOR                                                        
		SELECT   iRowId,inv_no,inv_date,qty_in, qty_out, cost FROM asBatch where code= @vchCodeX  and (qty_in-qty_out)>0 order by inv_date,QTY_BALANCE,IROWID
		OPEN asBatch 
		FETCH asBatch INTO @iRowId,@vchInv_no,@vchInv_date,	@vchqty_in, @vchqty_out, @vchcost
		set @UnitCount=1
		set @QTYvar=0
		set @qtyVar=@vchqty_inx
		set @CostvarNew = @vchcost
		WHILE @UnitCount = 1
		   BEGIN
		set @CostvarNew = @vchcost
				if (@vchqty_in - @vchqty_out)>=@qtyVar
				begin
					insert into assales (code,inv_no,batch,inv_date,deptno, qty_in,qty_out, qty_balance, cost, cost_avg) values(@vchCodeX, @vchInv_noX, @vchInv_no,@vchInv_dateX,'',@qtyVar,0,0,@CostvarNew,0)
					UPDATE asBatch SET QTY_OUT= QTY_OUT + @qtyVar WHERE IROWID=@IROWID
					set @unitCount=0
					set @qtyVar= 0
				end
			else
				begin
					insert into assales (code,inv_no,batch,inv_date,deptno, qty_in,qty_out, qty_balance, cost, cost_avg) values(@vchCodeX, @vchInv_noX, @vchInv_no,@vchInv_dateX,'x',(@vchqty_in - @vchqty_out),0,0,@CostvarNew,0)
					
					UPDATE asBatch SET QTY_OUT= QTY_OUT + (@vchqty_in - @vchqty_out) WHERE IROWID=@IROWID
					set @qtyVar= @qtyVar - (@vchqty_in - @vchqty_out)
				end
		   FETCH asBatch INTO @iRowId, @vchInv_no, @vchInv_date,@vchqty_in,	@vchqty_out, @vchcost
		   END
		CLOSE asBatch
		DEALLOCATE asBatch
    FETCH asSalesBatch INTO @iRowIdx, @vchInv_nox,@vchBatchx, @vchInv_datex,@vchqty_inx,	@vchqty_outx, @vchcostx, @vchcost_avgx,@vchCodeX
   END
CLOSE asSalesBatch
DEALLOCATE asSalesBatch
RETURN







